Hi,

I have created a Customer table with Country column and inserted 4 rows with country names (Germany, Mexico and Angola)

When I execute the following query

        SELECT *FROMCustomersWHERECountryLIKE'[!agm]%'

result set is showing all 4 rows but I read like the above query should

select all customers with a Country NOT starting  with "a", "g", or "m":

Please help me

correct me if I am wrong.

Thanks,

 

Vinodh,

Check if the following helps you:

declare @tab table (country varchar(100))
insert @tab select 'Angola'
insert @tab select 'Mexico'
insert @tab select 'Germany'
insert @tab select 'India'
insert @tab select 'Australia'

--Not starting with a,g,m
--method 1
select country
from @tab
where country like '[^agm]%'

--method 2
select country
from @tab
where country not like '[agm]%'

--starting with a,g,m
select country
from @tab
where country like '[agm]%'

I think you are looking for the below:

create table Customers1(Country varchar(100))
Insert into Customers1 values ('Germany'),('Mexico'),('Angola'),('USA'),('India'),('Australia')


SELECT * FROM Customers1 WHERE Country LIKE'[^agm]%'

Drop table Customers1

• Marked as answer by Vinodh Anandhan 2 hours 44 minutes ago

Hi Jay,

Thanks, Your reply is working fine. Please tell me what's wrong in my query.

Hi Vinodh

You should use ^ instead !

Below will work for you:

 SELECT * FROM Customers WHERE Country LIKE '[^agm]%'

I am not sure where you read about "!" , if you have please share that

Hi Jay,

Thanks,

SELECT *FROMCustomersWHERECountryLIKE'[^agm]%'

is working fine.

 

The below notation is wrong:

  SELECT *FROMCustomersWHERECountryLIKE'[!agm]%'

[!] character wont do the intended functionality, rather you should use [^] character.

• Proposed as answer by Saurabh Sinha DBAMicrosoft community contributor 2 hours 42 minutes ago

Vinodh,

Sorry for the late reply. Yes, as Latheesh and Saurabh pointed out, usage of '^' instead of '!' is the corrective action. We dont use '!' in cas eof wildcard specifications..

Great to know tht the query is working! Cheers!